/**
 * 
 */
package org.aergia.vinny.database;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.persistence.EntityManager;
import javax.persistence.EntityTransaction;
import javax.persistence.Query;
import javax.persistence.TypedQuery;

import org.aergia.vinny.core.ApiResult;
import org.aergia.vinny.core.enums.DayOfWeek;
import org.aergia.vinny.database.pojo.Card;
import org.aergia.vinny.database.pojo.Location;
import org.aergia.vinny.database.pojo.OperatingHours;
import org.aergia.vinny.database.pojo.Organization;
import org.aergia.vinny.database.pojo.Scan;
import org.aergia.vinny.database.pojo.Service;
import org.aergia.vinny.database.pojo.ServiceType;
import org.aergia.vinny.database.pojo.Skill;
import org.aergia.vinny.database.pojo.SkillRate;
import org.aergia.vinny.database.pojo.VinnyOption;
import org.aergia.vinny.database.pojo.VinnyRole;
import org.aergia.vinny.database.pojo.VinnyUser;
import org.aergia.vinny.database.pojo.Volunteer;
import org.aergia.vinny.database.pojo.VolunteerGroup;
import org.aergia.vinny.database.pojo.Workstation;
import org.aergia.vinny.util.CollectionsUtil;
import org.apache.openjpa.persistence.OpenJPAEntityManager;
import org.apache.openjpa.persistence.OpenJPAPersistence;

/**
 * @author Fred
 */
public class DatabaseImpl implements Database {

	private final EntityManager em;
	private final Logger logger;

	public DatabaseImpl(final EntityManager em) {
		this.em = em;
		this.logger = Logger.getLogger(DatabaseImpl.class.getName());
	}

	@Override
	public ApiResult<Card> addCard(final Card card) {
		card.setActive(true);
		return this.persist(card, "Error adding card");
	}

	@Override
	public ApiResult<VolunteerGroup> addGroup(final VolunteerGroup group) {
		group.setActive(true);
		return this.persist(group, "Error adding group.");
	}

	@Override
	public ApiResult<Location> addLocation(final Location location) {
		location.setActive(true);
		return this.persist(location, "Error adding location");
	}

	@Override
	public ApiResult<OperatingHours> addOperatingHours(final OperatingHours operatingHours) {
		operatingHours.setActive(true);
		return this.persist(operatingHours, "Error adding operating hours");
	}

	@Override
	public ApiResult<Organization> addOrganization(final Organization organization) {
		organization.setActive(true);
		return this.persist(organization, "Error adding organization");
	}

	@Override
	public ApiResult<VinnyRole> addRole(final VinnyRole role) {
		role.setActive(true);
		return this.persist(role, "Error adding role");
	}

	@Override
	public ApiResult<Scan> addScan(final Scan scan) {
		scan.setActive(true);
		return this.persist(scan, "Error adding scan");
	}

	@Override
	public ApiResult<Service> addService(final Service service) {
		service.setActive(true);
		return this.persist(service, "Error adding service");
	}

	@Override
	public ApiResult<ServiceType> addServiceType(final ServiceType serviceType) {
		serviceType.setActive(true);
		return this.persist(serviceType, "Error adding serviceType");
	}

	@Override
	public ApiResult<Skill> addSkill(final Skill skill) {
		skill.setActive(true);
		return this.persist(skill, "Error adding skill");
	}

	@Override
	public ApiResult<SkillRate> addSkillRate(final SkillRate skillRate) {
		skillRate.setActive(true);
		return this.persist(skillRate, "Error adding skill rate for " + skillRate.getSkill().getDescription());
	}

	@Override
	public ApiResult<VinnyUser> addUser(final VinnyUser newUser) {
		newUser.setActive(true);
		return this.persist(newUser, "Error adding vinny user");
	}

	@Override
	public ApiResult<Volunteer> addVolunteer(final Volunteer volunteer) {
		volunteer.setActive(true);
		return this.persist(volunteer, "Error adding volunteer");
	}

	@Override
	public ApiResult<Workstation> addWorkstation(final Workstation workstation) {
		workstation.setActive(true);
		return this.persist(workstation, "Error adding workstation");
	}

	@Override
	public ApiResult<VinnyUser> deleteUser(final VinnyUser deleteUser) {
		deleteUser.setActive(false);
		return this.persist(deleteUser, "Error removing user");
	}

	@Override
	public ApiResult<Volunteer> deleteVolunteer(final Volunteer volunteer) {
		volunteer.setActive(false);
		return this.persist(volunteer, "Error removing volunteer");
	}

	@Override
	public ApiResult<Card> getCard(final String cardNumber) {
		return this.getByKey(Card.class, "select X from Card X where X.number = '" + cardNumber + "'", "Could not find card with number of " + cardNumber,
				"Error getting card for " + cardNumber);
	}

	@Override
	public Collection<? extends Card> getCards() {
		return this.getAll(Card.class, "select X from Card X", "Error retrieving cards.");
	}

	@Override
	public Connection getConnection() {
		final OpenJPAEntityManager kem = OpenJPAPersistence.cast(this.em);
		final Connection conn = (Connection) kem.getConnection();
		return conn;
	}

	@Override
	public ApiResult<VolunteerGroup> getGroup(final String groupName, final Location location) {
		return this.getByKey(VolunteerGroup.class, "select X from VolunteerGroup X where X.name = '" + groupName + "' and X.location.id = " + location.getId(),
				"Could not find volunteer group with name of " + groupName, "Error getting volunteer group for " + groupName);
	}

	@Override
	public Collection<? extends VolunteerGroup> getGroups() {
		return this.getAll(VolunteerGroup.class, "select X from VolunteerGroup X", "Error retrieving volunteer groups.");
	}

	/*
	 * (non-Javadoc)
	 * @see org.aergia.vinny.database.Database#getLocation(int)
	 */
	@Override
	public ApiResult<Location> getLocation(final int id) {
		return this.get(Location.class, Integer.valueOf(id), "Could not find location with ID of " + id);
	}

	/*
	 * (non-Javadoc)
	 * @see org.aergia.vinny.database.Database#getLocation(java.lang.String)
	 */
	@Override
	public ApiResult<Location> getLocation(final String locName) {
		return this.getByKey(Location.class, "select X from Location X where X.name='" + locName + "'", "Could not find location with name of " + locName,
				"Error getting location for " + locName);
	}

	@Override
	public Collection<? extends Location> getLocations() {
		return this.getAll(Location.class, "select X from Location X", "Error retrieving locations.");
	}

	@Override
	public Collection<? extends OperatingHours> getOperatingHours() {
		return this.getAll(OperatingHours.class, "select X from OperatingHours X", "Error retrieving operating hours.");
	}

	@Override
	public ApiResult<OperatingHours> getOperatingHours(final DayOfWeek day) {
		try {
			final TypedQuery<OperatingHours> query = this.em.createQuery("select X from OperatingHours X where X.day=" + day.getValue(), OperatingHours.class);
			final Collection<OperatingHours> list = query.getResultList();
			if (list.size() == 0) {
				return new ApiResult<OperatingHours>(true, "Could not find operating hours for " + day.toString());
			}
			return new ApiResult<OperatingHours>(CollectionsUtil.getFirst(list));
		} catch (final Exception e) {
			this.logger.severe("Could not find opearing hours " + day.toString() + ": " + e.getLocalizedMessage());
			return new ApiResult<OperatingHours>(true, "Could not find opearting hours for " + day.toString() + ": " + e.getLocalizedMessage());
		}
	}

	@Override
	public ApiResult<VinnyOption> getOption(final String parameterName) {
		return this.getByKey(VinnyOption.class, "select X from VinnyOption X where X.parameter='" + parameterName + "'", "Could not find option for "
				+ parameterName, "Error getting option for " + parameterName);
	}

	@Override
	public Collection<? extends VinnyOption> getOptions() {
		return this.getAll(VinnyOption.class, "select X from VinnyOption X", "Error retrieving options.");
	}

	/*
	 * (non-Javadoc)
	 * @see org.aergia.vinny.database.Database#getOrganization(java.lang.String)
	 */
	@Override
	public ApiResult<Organization> getOrganization(final String name) {
		return this.getByKey(Organization.class, "select X from Organization X where X.name = '" + name + "'", "Could not find organization with name of "
				+ name, "Error finding organization for " + name);
	}

	/*
	 * (non-Javadoc)
	 * @see org.aergia.vinny.database.Database#getOrganizations()
	 */
	@SuppressWarnings("unchecked")
	@Override
	public Collection<Organization> getOrganizations() {
		try {
			final Query query = this.em.createQuery("select X from Organization X where X.active = true", Organization.class);
			return query.getResultList();
		} catch (final Exception e) {
			this.logger.severe("Error retieving unmatched scans: " + e.getMessage());
			this.logger.log(Level.FINE, "Error retieving unmatched scans", e);
			return new ArrayList<Organization>();
		}
	}

	@Override
	public ApiResult<VinnyRole> getRole(final String permission) {
		return this.getByKey(VinnyRole.class, "select X from VinnyRole X where X.permission = '" + permission + "'", "Could not find skill with permission of "
				+ permission + ".", "Error finding skill for " + permission + ".");
	}

	@Override
	public Collection<? extends VinnyRole> getRoles() {
		return this.getAll(VinnyRole.class, "select X from VinnyRole X", "Error retrieving roles.");
	}

	@Override
	public Collection<? extends Scan> getScans() {
		return this.getAll(Scan.class, "select X from Scan X", "Error retrieving scans.");
	}

	@Override
	public Collection<? extends Service> getServices() {
		return this.getAll(Service.class, "select X from Service X", "Error retrieving services.");
	}

	@Override
	public ApiResult<ServiceType> getServiceType(final String name, final Location location) {
		return this.getByKey(ServiceType.class, "select X from ServiceType X where X.name='" + name + "' and X.location.id=" + location.getId(),
				"Could not find " + name + " service type.", "Error retrieving " + name + " service type.");
	}

	@Override
	public Collection<? extends ServiceType> getServiceTypes() {
		return this.getAll(ServiceType.class, "select X from ServiceType X", "Error retrieving service types.");
	}

	@Override
	public ApiResult<Skill> getSkill(final String description) {
		return this.getByKey(Skill.class, "select X from Skill X where X.description = '" + description + "'", "Could not find skill with description of "
				+ description, "Error finding skill for " + description);
	}

	@Override
	public ApiResult<SkillRate> getSkillRate(final Skill skill) {
		return this.get(SkillRate.class, skill, "Error finding skill rate for");
	}

	@Override
	public Collection<? extends SkillRate> getSkillRates() {
		return this.getAll(SkillRate.class, "select X from SkillRate X", "Error retrieving skill rates.");
	}

	@Override
	public Collection<? extends Skill> getSkills() {
		return this.getAll(Skill.class, "select X from Skill X", "Error retrieving skills.");
	}

	@SuppressWarnings("unchecked")
	@Override
	public Collection<Service> getUnapprovedServices() {
		try {
			final Query query = this.em.createQuery("select X from Service X where X.active = true and X.approvedBy is null", Service.class);
			return query.getResultList();
		} catch (final Exception e) {
			this.logger.severe("Error retieving unmatched scans: " + e.getMessage());
			this.logger.log(Level.FINE, "Error retieving unmatched scans", e);
			return new ArrayList<Service>();
		}
	}

	/*
	 * (non-Javadoc)
	 * @see org.aergia.vinny.database.Database#getUnmatchedScans()
	 */
	@SuppressWarnings("unchecked")
	@Override
	public Collection<Scan> getUnmatchedScans() {
		try {
			final Query query = this.em.createQuery("select X from Scan X where X.active = true and X.service is null", Scan.class);
			return query.getResultList();
		} catch (final Exception e) {
			this.logger.severe("Error retieving unmatched scans: " + e.getMessage());
			this.logger.log(Level.FINE, "Error retieving unmatched scans", e);
			return new ArrayList<Scan>();
		}
	}

	@Override
	public ApiResult<VinnyUser> getUser(final String userName) {
		return this.getByKey(VinnyUser.class, "select X from VinnyUser X where X.active = true and X.userName = '" + userName + "\'", "Error finding user "
				+ userName, "Error retrieving user for " + userName);
	}

	@Override
	public Collection<? extends VinnyUser> getUsers() {
		return this.getAll(VinnyUser.class, "select X from VinnyUser X", "Error retrieving users.");
	}

	@Override
	public Collection<VinnyUser> getUsers(final String inRole) {
		final Collection<VinnyUser> result = new ArrayList<VinnyUser>();
		final Collection<VinnyUser> users = this.getAll(VinnyUser.class, "select X from VinnyUser X where X.active = true", "Error retrieving users");
		for (final VinnyUser user : users) {
			if (user.inRole(inRole.toString())) {
				result.add(user);
			}
		}
		return result;
	}

	@Override
	public ApiResult<Volunteer> getVolunteer(final int id) {
		return this.get(Volunteer.class, Integer.valueOf(id), "Error finding volunteer for ID");
	}

	@Override
	public Collection<? extends Volunteer> getVolunteers() {
		return this.getAll(Volunteer.class, "select X from Volunteer X", "Error retrieving volunteers.");
	}

	@Override
	public Collection<Volunteer> getVolunteers(final VolunteerGroup group) {
		return this.getAll(Volunteer.class, "select X from Volunteer X where X.group.id=" + group.getId(), "Error retrieving volunteers.");
	}

	@Override
	public ApiResult<Workstation> getWorkstation(final int id) {
		return this.get(Workstation.class, Integer.valueOf(id), "Error finding workstation for ID");
	}

	@Override
	public ApiResult<Workstation> getWorkstation(final String name, final Location location) {
		return this.getByKey(Workstation.class, "select X from Workstation X where X.location.id=" + location.getId() + " and X.name='" + name + "'",
				"Error finding workstation " + name + ".", "Error retrieving workstation for " + name + ".");
	}

	@Override
	public Collection<? extends Workstation> getWorkstations() {
		return this.getAll(Workstation.class, "select X from Workstation X", "Error retrieving workstations.");
	}

	@Override
	public Collection<? extends Workstation> getWorkstations(final Location location) {
		return this.getAll(Workstation.class, "select X from Workstation X where X.location.id=" + location.getId(),
				"Error retrieving workstation for location " + location.getName() + ".");
	}

	/*
	 * (non-Javadoc)
	 * @see org.aergia.vinny.database.Database#isInitialized()
	 */
	@Override
	public boolean isInitialized() {
		return this.getOrganizations().size() != 0;
	}

	@Override
	public ApiResult<Card> saveCard(final Card card) {
		return this.merge(card, "Error saving card");
	}

	@Override
	public ApiResult<VolunteerGroup> saveGroup(final VolunteerGroup group) {
		return this.merge(group, "Error saving volunteer group");
	}

	@Override
	public ApiResult<OperatingHours> saveOperatingHours(final OperatingHours newHours) {
		return this.merge(newHours, "Error saving newHours");
	}

	@Override
	public ApiResult<VinnyOption> saveOption(final VinnyOption option) {
		return this.merge(option, "Error saving options");
	}

	@Override
	public ApiResult<Scan> saveScan(final Scan scan) {
		return this.merge(scan, "Error saving scan");
	}

	@Override
	public ApiResult<Service> saveService(final Service service) {
		return this.merge(service, "Error saving service");
	}

	@Override
	public ApiResult<ServiceType> saveServiceType(final ServiceType serviceType) {
		return this.merge(serviceType, "Error saving serviceType");
	}

	@Override
	public ApiResult<SkillRate> saveSkillRate(final SkillRate skillRate) {
		return this.merge(skillRate, "Error saving skillRate");
	}

	@Override
	public ApiResult<VinnyUser> saveUser(final VinnyUser saveUser) {
		return this.merge(saveUser, "Error saving user");
	}

	@Override
	public ApiResult<Volunteer> saveVolunteer(final Volunteer volunteer) {
		return this.merge(volunteer, "Error saving volunteer");
	}

	@Override
	public ApiResult<Workstation> saveWorkstation(final Workstation workstation) {
		return this.merge(workstation, "Error saving workstation");
	}

	@Override
	public void setOption(final VinnyOption option) {
		final ApiResult<VinnyOption> result = this.getOption(option.getParameter());
		if (result.isError()) {
			this.persist(option, "Error adding option for");
		} else {
			this.merge(option, "Error saving option for ");
		}
	}

	private <T extends Object> ApiResult<T> get(final Class<T> clazz, final Object key, final String errorMessage) {
		try {
			final T value = this.em.find(clazz, key);
			if (value == null) {
				return new ApiResult<T>(true, key + " not found for " + clazz.getName());
			}
			return new ApiResult<T>(value);
		} catch (final Exception e) {
			this.logger.severe(errorMessage + " " + key + ": " + e.getLocalizedMessage());
			this.logger.log(Level.FINE, errorMessage + " " + key, e);
			return new ApiResult<T>(true, errorMessage + " " + key + " " + e.getMessage());
		}
	}

	@SuppressWarnings("unchecked")
	private <T extends Object> List<T> getAll(final Class<T> clazz, final String selectStatement, final String errorMsg) {
		try {
			final Query query = this.em.createQuery(selectStatement, clazz);

			final List<Object> items = query.getResultList();
			final List<T> values = new ArrayList<T>((List<T>) items);
			return values;
		} catch (final Exception e) {
			this.logger.severe(errorMsg + ": " + e.getMessage());
			this.logger.log(Level.FINE, errorMsg, e);
			return new ArrayList<T>();
		}
	}

	@SuppressWarnings("unchecked")
	private <T extends Object> ApiResult<T> getByKey(final Class<T> clazz, final String selectStatement, final String noFindMsg, final String errorMsg) {
		try {
			final Query query = this.em.createQuery(selectStatement, clazz);

			final List<T> results = query.getResultList();
			if (results.isEmpty()) {
				return new ApiResult<T>(true, noFindMsg);
			}
			return new ApiResult<T>(CollectionsUtil.getFirst(results));
		} catch (final Exception e) {
			this.logger.severe(errorMsg + ": " + e.getMessage());
			this.logger.log(Level.FINE, errorMsg, e);
			return new ApiResult<T>(true, errorMsg + ": " + e.getMessage());
		}
	}

	/**
	 * @param object
	 * @throws Exception
	 */
	private <T extends Object> ApiResult<T> merge(final T object, final String errorMsg) {
		try {
			final EntityTransaction et = this.em.getTransaction();
			et.begin();
			this.em.merge(object);
			et.commit();
			return new ApiResult<T>(object);
		} catch (final Exception e) {
			this.logger.severe(errorMsg + ": " + e.getMessage());
			this.logger.log(Level.FINE, errorMsg, e);
			return new ApiResult<T>(true, errorMsg + e.getMessage());
		}
	}

	/**
	 * @param object
	 * @throws Exception
	 */
	private <T extends Object> ApiResult<T> persist(final T object, final String errorMsg) {
		try {
			final EntityTransaction et = this.em.getTransaction();
			et.begin();
			this.em.persist(object);
			et.commit();
			return new ApiResult<T>(object);
		} catch (final Exception e) {
			this.logger.severe(errorMsg + ": " + e.getMessage());
			this.logger.log(Level.FINE, errorMsg, e);
			return new ApiResult<T>(true, errorMsg + ": " + e.getMessage());
		}
	}
}
